﻿using Model;
using System.Data;

namespace BLL
{
    /// <summary>
    /// 计算日利息方法
    /// </summary>
    public class Yx_DayInterestBLL : BaseBLL
    {
        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertModel(Yx_DayInterest model, string operatename)
        {
            model.ID = GetSequence("DAYINTERESTID");
            string sql = string.Format(@"insert into yx_dayinterest(id, companyid, balancemoney, bdate, createdayinterest)
values(" + model.ID + ", " + model.CompanyId + ", " + model.BalanceMoney + ", to_date('" + model.BDate + "','yyyy-MM-dd'), " + model.CreDayInterest + ")");

            int count = NoQuery(sql, operatename);

            if (count > 0)
            {
                return count;
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// 根据条件查询日利息明细
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable SelectByCon(string condition)
        {
            string sql = string.Format(@"select 公司名称 公司名称,交易日期 交易日期, 账户余额 账户余额,收入户利息 收入户利息,支出户利息 支出户利息
                from (select c.cmy_name 公司名称,d.bdate 交易日期,d.balancemoney 账户余额,
                nvl(decode(d.accounttype, 1, d.createdayinterest,null),0) 支出户利息,
                nvl(decode(d.accounttype,2,d.createdayinterest,null),0) 收入户利息
                from yx_dayinterest d, sys_company c
                where d.companyid = c.cmy_id ");

            if (condition != "")
            {
                sql += " " + condition + "";
            }
            sql += " order by d.companyid)";

            sql += @" union all
            select '合计', null 交易日期,sum(d.balancemoney) 账户余额,
            nvl(sum(decode(d.accounttype,2,d.createdayinterest,null)),0) 收入户利息,
            nvl(sum(decode(d.accounttype, 1, d.createdayinterest,null)),0) 支出户利息
            from yx_dayinterest d, sys_company c
            where d.companyid = c.cmy_id  ";
            if (condition != "")
            {
                sql += " " + condition + "";
            }

            DataTable dt = Query(sql);
            return dt;
        }

        /// <summary>
        /// 日利息统计
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable SelectReprot(string condition)
        {
            string sql = string.Format(@" select cmy_name 公司名称,收入户 收入户,支出户 支出户,(收入户-支出户) 利息合计 from (
               select  d.companyid,c.cmy_name,nvl(sum(decode(d.accounttype,'2',d.createdayinterest,null)),0) 收入户,
               nvl(sum(decode(d.accounttype,'1',d.createdayinterest,null)),0) 支出户
               from yx_dayinterest d,sys_company c
               where d.companyid=c.cmy_id  and 1=1 " + condition + @"
               group by  d.companyid,c.cmy_name order by d.companyid )
               union all
               select '合计',null 收入户,null 支出户,sum(sum(decode(d.accounttype, '2', d.createdayinterest, null)) - sum(decode(d.accounttype, '1', d.createdayinterest, null))) 利息合计
               from yx_dayinterest d, sys_company c
               where d.companyid = c.cmy_id
               and 1 = 1 " + condition + @"
               group by d.companyid, c.cmy_name");

            DataTable dt = Query(sql);
            return dt;
        }
    }
}